package com.zero.manage.web.utils.generate.service;

import com.zero.manage.web.utils.CommonUtils;
import com.zero.manage.web.utils.generate.ColumnInfo;
import com.zero.manage.web.utils.generate.DatabaseDao;
import com.zero.manage.web.utils.generate.SqlServerDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.*;

/**
 * SqlServer数据库相关操作
 *
 * @author zero
 */
@Service
public class SqlServerService implements DatabaseDao {
    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private SqlServerDao sqlServerDao;

    @Override
    public List<String> getCatalogs() {
        return sqlServerDao.getCatalogs();
    }

    @Override
    public List<Map<Object, Object>> getTableInfo(String catalogs) {
        List<Map<Object, Object>> list = new ArrayList<>(1);
        List<Object[]> resultList = entityManager.createNativeQuery("USE " + catalogs
                + " select cast(so.name as varchar(500)) as tableName,cast(sep.value as varchar(500)) as tableComment"
                + " from sysobjects so left JOIN sys.extended_properties sep on sep.major_id=so.id and sep.minor_id=0"
                + " where (xtype='U' or xtype='v')").getResultList();
        for (Object[] objArr : resultList) {
            Map<Object, Object> map = new HashMap<>(resultList.size());
            map.put("tableName", objArr[0]);
            map.put("tableComment", objArr[1]);
            list.add(map);
        }
        return list;
    }

    @Override
    public List<ColumnInfo> getColumns(String catalogs, String tableName) {
        List<Object[]> resultList = entityManager.createNativeQuery("USE " + catalogs
                + " SELECT cast(b.name AS VARCHAR(500)) AS COLUMN_NAME,"
                + " cast(c.VALUE AS NVARCHAR(500)) AS COMMENTS,cast(sys.types.name AS VARCHAR (500)) AS DATA_TYPE,"
                + "(" + " SELECT CASE count(1) WHEN 1 then 'PRI' ELSE '' END"
                + " FROM syscolumns,sysobjects,sysindexes,sysindexkeys,systypes "
                + " WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = object_id (a.name) AND sysobjects.xtype = 'PK'"
                + " AND sysobjects.parent_obj = syscolumns.id " + " AND sysindexes.id = syscolumns.id "
                + " AND sysobjects.name = sysindexes.name AND sysindexkeys.id = syscolumns.id "
                + " AND sysindexkeys.indid = sysindexes.indid "
                + " AND syscolumns.colid = sysindexkeys.colid AND syscolumns.name = b.name) as 'KEY'"
                + " FROM ( select name,object_id from sys.tables UNION all select name,object_id from sys.views ) a "
                + " INNER JOIN sys.columns b ON b.object_id = a.object_id "
                + " LEFT JOIN sys.types ON b.user_type_id = sys.types.user_type_id   "
                + " LEFT JOIN sys.extended_properties c ON c.major_id = b.object_id AND c.minor_id = b.column_id "
                + " WHERE a.name = '" + tableName + "' and sys.types.name !='sysname' ").getResultList();
        List<ColumnInfo> list = new ArrayList<>();
        for (Object[] objArr : resultList) {
            ColumnInfo build = ColumnInfo.builder()
                    .columnName(objArr[0].toString())
                    .remarks(CommonUtils.handleObjTurnStr(objArr[1]))
                    .typeName(objArr[2].toString())
                    .isPrimaryKey(Objects.equals("PRI", CommonUtils.handleObjTurnStr(objArr[3])))
                    .build();
            list.add(build);
        }
        return list;
    }
}
